﻿Imports System
Imports System.Data
Imports System.Text
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Security.Cryptography
Imports System.Data.OleDb
Imports System.Threading
Imports System.Globalization

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.Xml


Imports System.Security
Imports System.Security.Principal.WindowsIdentity


Public Class Form1

    Private dataAdapter As New SqlClient.SqlDataAdapter()
    Private bindingSource1 As New BindingSource()

    Private db As New DataClasses1DataContext

    Public Sub New()

        Startup.ShowDialog()
        'Startup.Show()

        Thread.CurrentThread.CurrentUICulture = New CultureInfo(My.Settings.Language)
        InitializeComponent()

    End Sub


    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Form1Size = Me.Size
        My.Settings.Form1Pos = Me.Location
        My.Settings.DateFrom = Me.DateTimePicker1.Value
        My.Settings.DateTo = Me.DateTimePicker2.Value


        'Save all gridview setups
        If Not Directory.Exists(My.Settings.ConfigPath) Then Directory.CreateDirectory(My.Settings.ConfigPath)

        Try
            GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\GW01.xml")
            GridView2.SaveLayoutToXml(My.Settings.ConfigPath & "\GW02.xml")
            GridView3.SaveLayoutToXml(My.Settings.ConfigPath & "\GW03.xml")
            GridView4.SaveLayoutToXml(My.Settings.ConfigPath & "\GW04.xml")
        Catch ex As Exception

        End Try


    End Sub

    Private Sub Form1_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.GotFocus
        Graf.BringToFront()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        BSTSize.Caption = ScreenResolution()

        'Get setups informations
        Me.Size = My.Settings.Form1Size
        FormPos(My.Settings.Form1Pos)
        Me.Location = My.Settings.Form1Pos
        My.Settings.PDFPath = GetSetupValue("PDFPath")
        My.Settings.sPathtoM3 = GetSetupValue("sPathtoM3")
        My.Settings.EmailFrom = GetSetupValue("EmailFrom")
        My.Settings.EmailServer = GetSetupValue("EmailServer")
        My.Settings.Gauge1Max = GetSetupValue("GaugeMax1")
        My.Settings.Gauge2Max = GetSetupValue("GaugeMax2")
        My.Settings.Gauge3Max = GetSetupValue("GaugeMax3")
        My.Settings.Gauge4Max = GetSetupValue("GaugeMax4")
        BarStaticItem1.Caption = GetSetupValue("CompanyName")

        My.Settings.sPathToDispatch = GetSetupValue("sPathToDispatch")
        My.Settings.PFleet = GetSetupValue("PFleet")
        My.Settings.Proxy = GetSetupValue("Proxy")
        My.Settings.ProxyPort = GetSetupValue("ProxyPort")
        My.Settings.PFleetCity = GetSetupValue("PFleetCity")
        My.Settings.MinZoom = GetSetupValue("MinZoom")
        My.Settings.MaxZoom = GetSetupValue("MaxZoom")
        My.Settings.Zoom = GetSetupValue("Zoom")
        My.Settings.CadisEnv = GetSetupValue("CadisEnv")

        My.Settings.Check_Small_Mat = GetSetupValue("Check_Small_Mat")

        CheckEnv()

        'Get build version
        Try
            Dim m_xmld = New XmlDocument()
            m_xmld.Load(Application.ExecutablePath & ".manifest")
            Me.Info.Caption = "JLD004 Version: " & m_xmld.ChildNodes.Item(1).ChildNodes.Item(0).Attributes.GetNamedItem("version").Value
        Catch ex As Exception

        End Try

        Me.DateTimePicker3.Value = Date.Today
        GetActivity(Date.Today)

        'Restore all gridview setups
        If File.Exists(My.Settings.ConfigPath & "\GW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\GW01.xml")
        If File.Exists(My.Settings.ConfigPath & "\GW02.xml") Then GridView2.RestoreLayoutFromXml(My.Settings.ConfigPath & "\GW02.xml")
        If File.Exists(My.Settings.ConfigPath & "\GW03.xml") Then GridView3.RestoreLayoutFromXml(My.Settings.ConfigPath & "\GW03.xml")
        If File.Exists(My.Settings.ConfigPath & "\GW04.xml") Then GridView4.RestoreLayoutFromXml(My.Settings.ConfigPath & "\GW04.xml")

        Me.SplitContainerControl1.SplitterPosition = My.Settings.Form1split1
        Me.SplitContainerControl2.SplitterPosition = My.Settings.Form1split2

        'Load user skins
        'DevExpress.UserSkins.OfficeSkins.Register()
        DevExpress.UserSkins.BonusSkins.Register()
        DefaultLookAndFeel1.LookAndFeel.SkinName = My.Settings.Skin
        Me.RibbonPageGroup20.Text = My.Settings.Skin


        'Load gauge values

        Dim i As Integer

        i = GetGaugeValue("S11", "45")
        If My.Settings.Gauge1Max < i Then
            ArcScaleComponent1.MaxValue = i + 50
        Else
            ArcScaleComponent1.MaxValue = My.Settings.Gauge1Max
        End If
        ArcScaleComponent1.Value = i
        DigitalGauge1.Text = i

        i = GetGaugeValue("S91", "45")
        If My.Settings.Gauge2Max < i Then
            ArcScaleComponent2.MaxValue = i + 50
        Else
            ArcScaleComponent2.MaxValue = My.Settings.Gauge2Max
        End If
        ArcScaleComponent2.Value = i
        DigitalGauge2.Text = i

        i = GetGaugeValue("S16", "45")
        If My.Settings.Gauge3Max < i Then
            ArcScaleComponent3.MaxValue = i + 20
        Else
            ArcScaleComponent3.MaxValue = My.Settings.Gauge3Max
        End If
        ArcScaleComponent3.Value = i
        DigitalGauge3.Text = i

        i = Val(GetGaugeValue("S91", "30")) + Val(GetGaugeValue("S91", "40"))
        If My.Settings.Gauge4Max < i Then
            ArcScaleComponent4.MaxValue = i + 2000
        Else
            ArcScaleComponent4.MaxValue = My.Settings.Gauge4Max
        End If
        ArcScaleComponent4.Value = i
        DigitalGauge4.Text = i


        Dim y As String = Date.Today.Year.ToString
        Dim m As String = Date.Today.Month.ToString
        GroupControl4.Text = "Turn over kCHF month: " & m & " / " & y
        GetTurnoverValue(y, m, DigitalGauge5.Text, DigitalGauge6.Text, DigitalGauge7.Text, DigitalGauge8.Text, 0)

        Me.RibbonControl1.SelectedPage = RBTools

        Me.DateTimePicker1.Value = My.Settings.DateFrom
        Me.DateTimePicker2.Value = My.Settings.DateTo

        GetTableHeader("F0003")
        GetTableHeader("F0004")
        GetTableHeader("F0005")
        GetTableHeader("ETEIL")

        Me.Label3.Text = My.Settings.Env

        'Get logged in user 
        Dim rName As String = My.User.Name
        Dim lSlash As Integer = rName.LastIndexOf("\")
        Dim fName As String = rName.Substring(lSlash + 1)

        'Set security
        If GetActiveDirectoryUserGroups(fName, "JLD004_Admin") Then
            BarButtonItem24.Visibility = DevExpress.XtraBars.BarItemVisibility.Always
            Form2.Button4.Visible = True
            Form2.RibbonPageGroup3.Visible = True
            Me.RibbonPageGroup21.Visible = True
        Else
            BarButtonItem24.Visibility = DevExpress.XtraBars.BarItemVisibility.Never
            Form2.Button4.Visible = False
            Form2.RibbonPageGroup3.Visible = False
            Me.RibbonPageGroup21.Visible = False
        End If

        If GetActiveDirectoryUserGroups(fName, "JLD004_Tech") Then
            BarButtonItem25.Visibility = DevExpress.XtraBars.BarItemVisibility.Always
        Else
            BarButtonItem25.Visibility = DevExpress.XtraBars.BarItemVisibility.Never
        End If


        If GetActiveDirectoryUserGroups(fName, "JLD004_Inventory") Then
            RBInventory.Visible = True
        Else
            RBInventory.Visible = False
        End If

        If GetActiveDirectoryUserGroups(fName, "JLD004_ErrorList") Then
            RibbonPageGroup2.Visible = True
        Else
            RibbonPageGroup2.Visible = False
        End If

        If GetActiveDirectoryUserGroups(fName, "JLD004_Search") Then
            RibbonPageGroup16.Visible = True
        Else
            RibbonPageGroup16.Visible = False
        End If


        If GetActiveDirectoryUserGroups(fName, "JLD004_TechTrace") Then
            My.Settings.TechTrace = True
        Else
            My.Settings.TechTrace = False
        End If

        If GetActiveDirectoryUserGroups(fName, "JLD004_Dispatch") Then
            My.Settings.Dispatch = True
        Else
            My.Settings.Dispatch = False
        End If


        If GetActiveDirectoryUserGroups(fName, "JLD004_Address") Then
            My.Settings.Address = True
        Else
            My.Settings.Address = False
        End If

        If GetActiveDirectoryUserGroups(fName, "JLD004_Boni") Then
            My.Settings.JLD004_Boni = True
        Else
            My.Settings.JLD004_Boni = False
        End If

        If My.Settings.JLD004_Boni Then
            Me.RibbonPageGroup26.Visible = True
        Else
            Me.RibbonPageGroup26.Visible = False
        End If

    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()

    End Sub

    Private Sub FindToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim dtf As String
        Dim dtt As String
        dtf = Format(Me.DateTimePicker1.Value, "yyyy-MM-dd") & " 00:00:00.000"
        dtt = Format(Me.DateTimePicker2.Value, "yyyy-MM-dd") & " 23:59:59.000"


        Dim AUFQuery = From AUF In db.AUFs _
                       Where AUF.Auftragsnummer Like Me.TextBox1.Text.ToString _
                       And AUF.TimeDate >= dtf And AUF.TimeDate <= dtt
                       Select AUF

        Me.AUFBindingSource.DataSource = AUFQuery

    End Sub

    Private Sub TUERsDataGridView_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs)

        ' Me.tuerDataGridView.CurrentCell = Me.AUFDataGridView(e.ColumnIndex, e.RowIndex)
        'Context-Menü anzeigen
        If e.Button = Windows.Forms.MouseButtons.Right Then

            Me.ContextMenuStrip1.Show()
            Me.ContextMenuStrip1.Left = MousePosition.X
            Me.ContextMenuStrip1.Top = MousePosition.Y

        End If
    End Sub

    Private Sub P001ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringP001
        My.Settings.M3Connect = My.Settings.M3ConnectP001
        My.Settings.MySQL = My.Settings.MySQLP001

        My.Settings.Env = "P001"
        Me.Label3.Text = "Restart"
        My.Settings.Save()
        Application.Restart()

    End Sub

    Private Sub M001ToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringM001
        My.Settings.M3Connect = My.Settings.M3ConnectM001
        My.Settings.MySQL = My.Settings.MySQLM001

        My.Settings.Env = "M001"
        Me.Label3.Text = "Restart"
        My.Settings.Save()
        Application.Restart()

    End Sub

    Private Sub EditSerialToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim s As String
        Try
            's = AUFDataGridView.CurrentRow.Cells(6).Value.ToString
            s = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "ID")
            ' If AUFDataGridView.CurrentRow.Cells(2).Value.ToString = "R" Then
            If GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Direction") = "R" Then
                Dialog1.Label1.Text = s
                Dialog1.ShowDialog()
            End If

        Catch ex As Exception

        End Try


    End Sub

    Private Sub ResendToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim s As String
        Try
            s = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "ID")
            If GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Direction") = "R" Then
                Dialog2.Label1.Text = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsnummer")
                Dialog2.Label2.Text = s
                Dialog2.ShowDialog()
            End If
        Catch ex As Exception

        End Try

    End Sub

    Private Sub ConfigurationToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form3.Show()

    End Sub

    Private Sub ErrorListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form2.Show()

    End Sub

    Private Sub GetInventoryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form6.Show()
    End Sub

    Private Sub CheckOrderToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Dim s As String
        Try
            's = AUFDataGridView.CurrentRow.Cells(6).Value.ToString
            s = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "ID")
            If GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Direction") = "R" Then
                CheckAUF(s, GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsnummer"))
            End If
        Catch ex As Exception

        End Try




    End Sub

    Private Sub ShowLogsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form7.Show()

    End Sub

    Private Sub ResendToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)

        ' Dialog2.Label2.Text = AUFDataGridView.CurrentRow.Cells(6).Value.ToString
        'Dialog2.Label1.Text = AUFDataGridView.CurrentRow.Cells(0).Value.ToString
        Dialog2.Label2.Text = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "ID")
        Dialog2.Label1.Text = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsnummer")
        'If AUFDataGridView.CurrentRow.Cells(2).Value.ToString = "R" Then
        If GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Direction") = "R" Then
            Dialog2.Show()
        End If



    End Sub

    Private Sub GetWarrantyAndContractInformationToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetWarrantyAndContractInformationToolStripMenuItem.Click

        Dim a As Boolean
        Dim b As Boolean

        'Get order date
        'Dim dOrder As Date = AUFDataGridView.CurrentRow.Cells(3).Value.ToString
        Dim dOrder As Date = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "TimeDate")
        Dim sDate As String

        sDate = Format(dOrder, "yyyyMMdd")

        'CheckSM(TUERsDataGridView.CurrentRow.Cells(2).Value.ToString, sDate, AUFDataGridView.CurrentRow.Cells(4).Value.ToString, _
        '      TUERsDataGridView.CurrentRow.Cells(2).Value.ToString, Dialog4.Label1.Text, Dialog4.Label2.Text, Dialog4.Label3.Text, a, b)

        'GridView4.GetRowCellValue(GridView4.FocusedRowHandle, "ID")
        'CheckSM(TUERsDataGridView.CurrentRow.Cells(2).Value.ToString, sDate, GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsart"), _
        'TUERsDataGridView.CurrentRow.Cells(2).Value.ToString, Dialog4.Label1.Text, Dialog4.Label2.Text, Dialog4.Label3.Text, a, b)

        'AuftragsartTextBox
        'AuftragsartTextBox1

        'CheckSM(GridView4.GetRowCellValue(GridView4.FocusedRowHandle, "Seriennummer"), sDate, GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsart"), _
        'GridView4.GetRowCellValue(GridView4.FocusedRowHandle, "Türschlüssel"), Dialog4.Label1.Text, Dialog4.Label2.Text, Dialog4.Label3.Text, a, b)

        CheckSM(GridView4.GetRowCellValue(GridView4.FocusedRowHandle, "Seriennummer"), sDate, AuftragsartTextBox1.Text, _
        GridView4.GetRowCellValue(GridView4.FocusedRowHandle, "Türschlüssel"), Dialog4.Label1.Text, Dialog4.Label2.Text, Dialog4.Label3.Text, a, b)


        If a Then
            Dialog4.Label4.Text = "With"
        Else
            Dialog4.Label4.Text = "Without"
        End If

        If b Then
            Dialog4.Label5.Text = "With"
        Else
            Dialog4.Label5.Text = "Without"
        End If


        Dialog4.ShowDialog()

    End Sub

    Private Sub OpenAutoOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Auto_Order.Show()
    End Sub

    Private Sub NotInvoicedServicesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("40", "S11", "40 - Open services", "XTitle", "# of of open services")
    End Sub

    Private Sub OpenMaintenanceOrderToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("40", "S91", "40 - Open maintenances", "XTitle", "# of of open maintenances")

    End Sub

    Private Sub OpenReturnedServiceOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("45", "S11", "45 - Not invoiced services orders", "XTitle", "# of Not invoiced services orders")
    End Sub

    Private Sub OpenReturnedMaintenanceOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("45", "S91", "45 - Not booked maintenances orders", "XTitle", "# of Not booked maintenances orders")
    End Sub

    Private Sub ReadyToInvoiceServiceOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("60", "S11", "60 - ready to invoice services orders", "XTitle", "# of ready to invoice services orders")
    End Sub

    Private Sub ReadyToInvoiceMaintenenceOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("60", "S91", "60 - ready to invoice maintenances orders", "XTitle", "# of ready to invoice maintenances orders")

    End Sub

    Private Sub ReorderPointToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Reorder.Show()

    End Sub

    Private Sub EmptyPikettToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("40", "S16", "40 - reserve Pikett", "XTitle", "# of reserve Pikett")

    End Sub

    Private Sub OpendReturnedPikettToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("45", "S16", "45 - Not invoiced Pikett orders", "XTitle", "# of Not invoiced Pikett orders")

    End Sub

    Private Sub ReadyToInvoicePikettOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics("60", "S16", "60 - ready to invoice Pikett orders", "XTitle", "# of ready to invoice Pikett orders")

    End Sub

    Private Sub EmploiesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        StatisticTech.Show()

    End Sub

    Private Sub OpenAutoOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Auto_Order.Show()
    End Sub

    Private Sub ReorderPointToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Reorder.Show()
    End Sub


    Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("40", "S11", "40 - Open services", "XTitle", "# of of open services")

    End Sub

    Private Sub OpenReturnedServiceOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("45", "S11", "45 - Not invoiced services orders", "XTitle", "# of Not invoiced services orders")

    End Sub

    Private Sub ReadyToInvoiceServiceOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("60", "S11", "60 - ready to invoice services orders", "XTitle", "# of ready to invoice services orders")

    End Sub

    Private Sub OpenMaintenanceOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("40", "S91", "40 - Open maintenances", "XTitle", "# of of open maintenances")

    End Sub

    Private Sub OpenReturnedMaintenanceOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("45", "S91", "45 - Not booked maintenances orders", "XTitle", "# of Not booked maintenances orders")

    End Sub

    Private Sub ReadyToInvoiceMaintenenceOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("60", "S91", "60 - ready to invoice maintenances orders", "XTitle", "# of ready to invoice maintenances orders")

    End Sub

    Private Sub EmptyPikettToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("40", "S16", "40 - reserve Pikett", "XTitle", "# of reserve Pikett")

    End Sub

    Private Sub OpendReturnedPikettToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("45", "S16", "45 - Not invoiced Pikett orders", "XTitle", "# of Not invoiced Pikett orders")

    End Sub

    Private Sub ReadyToInvoicePikettOrdersToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics1("60", "S16", "60 - ready to invoice Pikett orders", "XTitle", "# of ready to invoice Pikett orders")

    End Sub

    Private Sub TestToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Statistics2()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        'Load gauge values
        ArcScaleComponent1.Value = GetGaugeValue("S11", "45")
        DigitalGauge1.Text = ArcScaleComponent1.Value
        ArcScaleComponent2.Value = GetGaugeValue("S91", "45")
        DigitalGauge2.Text = ArcScaleComponent2.Value
        ArcScaleComponent3.Value = GetGaugeValue("S16", "45")
        DigitalGauge3.Text = ArcScaleComponent3.Value
        ArcScaleComponent4.Value = Val(GetGaugeValue("S91", "30")) + Val(GetGaugeValue("S91", "40"))
        DigitalGauge4.Text = ArcScaleComponent4.Value

        Dim y As String = Date.Today.Year.ToString
        Dim m As String = Date.Today.Month.ToString
        GroupControl4.Text = "Turn over kSFR month: " & m & "/" & y
        GetTurnoverValue(y, m, DigitalGauge5.Text, DigitalGauge6.Text, DigitalGauge7.Text, DigitalGauge8.Text, 0)



        If Me.DateTimePicker3.Value = Date.Today Then
            GetActivity(Date.Today)
        End If

    End Sub


    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
        GetActivity(Me.DateTimePicker3.Value)
    End Sub


    Private Sub SearchToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form5.Show()
    End Sub

    Function RefreshTime()
        Dim startd As DateTime
        Dim stopd As DateTime
        Dim tsTimeSpan As TimeSpan
        Dim iNumberOfMinutes As Integer
        Dim iNumberOfHour As Integer

        Try

            startd = DateTime.ParseExact(EinsatzbeginnTextBox.Text, "yyyyMMddHHmm", Nothing)
            stopd = DateTime.ParseExact(EinsatzendeTextBox.Text, "yyyyMMddHHmm", Nothing)

            tsTimeSpan = stopd.Subtract(startd)
            iNumberOfMinutes = tsTimeSpan.Minutes
            iNumberOfHour = tsTimeSpan.Hours
            Label8.Text = iNumberOfHour.ToString & ":" & iNumberOfMinutes.ToString

        Catch ex As Exception

            Label8.Text = ""

        End Try

        RefreshTime = True

    End Function


    Private Sub EinsatzbeginnTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles EinsatzbeginnTextBox.TextChanged

        RefreshTime()


    End Sub

    Private Sub AUFDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
        RefreshTime()
    End Sub

    Private Sub ReportingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ActvityListToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form8.Show()
    End Sub

    Private Sub AveragePerOrderToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form9.Show()
    End Sub

    Private Sub TurnOverToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Form11.Show()

    End Sub

    Private Sub AddTechnitionToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Technicien.Show()

    End Sub

    Private Sub GridControl1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridControl1.DoubleClick
        Dim s As String = ""
        Dim s1 As String = ""
        Dim Mask As String = ""
        Dim sPDFfile As String = ""

        'Find the PDF file name

        Dim JLEXTNUM As String = Trim(GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Einsatznummer"))
        Dim JLAUF As String = Trim(GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Auftragsnummer"))

        s = GetPDFData("SELECT JLPDF FROM F5001 WHERE JLEXTNUM = '" & JLAUF & "' OR JLAUF = '" & JLAUF & "'")

        If s <> "" Then
            'Open PDF new methode
            Mask = My.Settings.PDFPath & "\" & s
            sPDFfile = Dir(Mask, vbNormal)
            If sPDFfile <> "" Then Process.Start(My.Settings.PDFPath & "\" & sPDFfile)

        Else
            'Open PDF old methode
            Try
                s = Trim(M3OrderNumberLabel1.Text.ToString)
                s = s.Substring(2, s.Length - 2)

                s1 = GridView1.GetRowCellValue(GridView1.FocusedRowHandle, "Einsatznummer")
                If s1.Substring(0, 1) = "f" Then
                    s = s1
                End If

                If s.Length < 4 Then
                    With GridView1
                        s = .GetRowCellValue(.FocusedRowHandle, "Auftragsnummer")
                    End With
                End If

                Mask = My.Settings.PDFPath & "\*" & s & "*.PDF"
                sPDFfile = Dir(Mask, vbNormal)
                If sPDFfile <> "" Then Process.Start(My.Settings.PDFPath & "\" & sPDFfile)

            Catch ex As Exception

            End Try
        End If
    End Sub

    Function GetPDFData(ByVal selectCommand As String)


        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        cmd.CommandText = selectCommand

        reader = cmd.ExecuteReader()

        If reader.Read Then
            GetPDFData = reader(0).ToString
        Else
            GetPDFData = ""
        End If

        sqlConnection1.Close()

    End Function

    Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress

        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
            Dim s = Me.TextBox1.Text.ToString

            Dim dtf As String
            Dim dtt As String
            dtf = Format(Me.DateTimePicker1.Value, "yyyy-MM-dd") & " 00:00:00.000"
            dtt = Format(Me.DateTimePicker2.Value, "yyyy-MM-dd") & " 23:59:59.000"


            Dim AUFQuery = From AUF In db.AUFs _
                           Where AUF.Auftragsnummer Like Me.TextBox1.Text.ToString _
                           And AUF.TimeDate >= dtf And AUF.TimeDate <= dtt
                           Select AUF

            Me.AUFBindingSource.DataSource = AUFQuery

        End If

    End Sub

    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress

        If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then

            Me.TextBox2.Text = Me.TextBox2.Text.ToString.ToUpper

            Dim AUFQuery = From AUF In db.AUFs
            Join M3Order In db.M3Orders
            On AUF.ID Equals M3Order.IDm
            Where M3Order.M3OrderNumber Like Me.TextBox2.Text.ToString.ToUpper
            Select AUF

            Me.AUFBindingSource.DataSource = AUFQuery

        End If

    End Sub

    Private Sub BarButtonItem1_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
        Dim dtf As String
        Dim dtt As String
        dtf = Format(Me.DateTimePicker1.Value, "yyyy-MM-dd") & " 00:00:00.000"
        dtt = Format(Me.DateTimePicker2.Value, "yyyy-MM-dd") & " 23:59:59.000"


        Dim AUFQuery = From AUF In db.AUFs _
                       Where AUF.Auftragsnummer Like Me.TextBox1.Text.ToString _
                       And AUF.TimeDate >= dtf And AUF.TimeDate <= dtt
                       Select AUF

        Me.AUFBindingSource.DataSource = AUFQuery

    End Sub

    Private Sub BarButtonItem2_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
        Form2.Show()

    End Sub

    Private Sub BarButtonItem3_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick
        Form6.Show()
    End Sub

    Private Sub BarButtonItem4_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem4.ItemClick
        Auto_Order.Show()
    End Sub

    Private Sub BarButtonItem5_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem5.ItemClick
        Reorder.Show()
    End Sub

    Private Sub BarButtonItem6_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem6.ItemClick
        StatisticTech.Show()
    End Sub

    Private Sub BarButtonItem7_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem7.ItemClick
        Statistics1("40", "S11", "40 - Open services", "XTitle", "# of of open services")
    End Sub

    Private Sub BarButtonItem8_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem8.ItemClick
        Statistics1("45", "S11", "45 - Not invoiced services orders", "XTitle", "# of Not invoiced services orders")
    End Sub

    Private Sub BarButtonItem9_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem9.ItemClick
        Statistics1("60", "S11", "60 - ready to invoice services orders", "XTitle", "# of ready to invoice services orders")
    End Sub

    Private Sub BarButtonItem10_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem10.ItemClick
        Statistics1("40", "S91", "40 - Open maintenances", "XTitle", "# of of open maintenances")
    End Sub

    Private Sub BarButtonItem11_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem11.ItemClick
        Statistics1("45", "S91", "45 - Not booked maintenances orders", "XTitle", "# of Not booked maintenances orders")
    End Sub

    Private Sub BarButtonItem12_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem12.ItemClick
        Statistics1("60", "S91", "60 - ready to invoice maintenances orders", "XTitle", "# of ready to invoice maintenances orders")
    End Sub

    Private Sub BarButtonItem13_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem13.ItemClick
        Statistics1("40", "S16", "40 - reserve Pikett", "XTitle", "# of reserve Pikett")
    End Sub

    Private Sub BarButtonItem14_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem14.ItemClick
        Statistics1("45", "S16", "45 - Not invoiced Pikett orders", "XTitle", "# of Not invoiced Pikett orders")
    End Sub

    Private Sub BarButtonItem15_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem15.ItemClick
        Statistics1("60", "S16", "60 - ready to invoice Pikett orders", "XTitle", "# of ready to invoice Pikett orders")
    End Sub

    Private Sub BarButtonItem16_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem16.ItemClick
        Statistics2()
    End Sub

    Private Sub BarButtonItem17_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem17.ItemClick
        Form8.Show()
    End Sub

    Private Sub BarButtonItem18_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem18.ItemClick
        Form9.Show()
    End Sub

    Private Sub BarButtonItem19_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem19.ItemClick
        Form11.Show()
    End Sub

    Private Sub BarButtonItem20_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs)
        My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringP001
        '  My.Settings.CADISConnectionString1 = My.Settings.CADISConnectionStringP001
        My.Settings.M3Connect = My.Settings.M3ConnectP001
        My.Settings.MySQL = My.Settings.MySQLP001

        My.Settings.Env = "P001"
        Me.Label3.Text = "Restart"
        My.Settings.Save()
        Application.Restart()
    End Sub

    Private Sub BarButtonItem21_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs)
        My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringM001
        '  My.Settings.CADISConnectionString1 = My.Settings.CADISConnectionStringM001
        My.Settings.M3Connect = My.Settings.M3ConnectM001
        My.Settings.MySQL = My.Settings.MySQLM001

        My.Settings.Env = "M001"
        Me.Label3.Text = "Restart"
        My.Settings.Save()
        Application.Restart()
    End Sub

    Private Sub BarButtonItem22_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem22.ItemClick
        Form7.Show()

    End Sub

    Private Sub BarButtonItem23_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem23.ItemClick
        Form5.Show()
    End Sub

    Private Sub BarButtonItem24_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem24.ItemClick
        Form3.Show()
    End Sub

    Private Sub BarButtonItem25_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem25.ItemClick
        Technicien.Show()
    End Sub


    Private Sub BarButtonItem29_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem29.ItemClick

        XtraTabControl1.SelectedTabPage = XtraTabPage3
        GMapForm.GMap(Adresszeile3TextBox1.Text.ToString, PLZTextBox1.Text.ToString, OrtTextBox1.Text.ToString)
        GMapForm.Show()

    End Sub

    Private Sub BarListItem2_ListItemClick_1(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ListItemClickEventArgs) Handles BarListItem2.ListItemClick
        DefaultLookAndFeel1.LookAndFeel.SkinName = (TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index)
        My.Settings.Skin = (TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index)
        Me.RibbonPageGroup20.Text = (TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index)

    End Sub

    Private Sub BarButtonItem30_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem30.ItemClick
        My.Settings.Language = "en-GB"
        My.Settings.Save()
        Application.Restart()
    End Sub

    Private Sub BarButtonItem31_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem31.ItemClick
        My.Settings.Language = "fr-CH"
        My.Settings.Save()
        Application.Restart()
    End Sub

    Private Sub BarButtonItem32_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem32.ItemClick
        UserForm.Show()

    End Sub

    Private Sub BarButtonItem33_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem33.ItemClick
        DataSourceForm.Show()

    End Sub

    Private Sub CheckEnv()

        Dim s() As String

        s = Split(GetCurrent.Name, "\")

        Dim M3Con As New System.Data.OleDb.OleDbConnection(My.Settings.M3ConnectUser)
        Dim M3Cmd As New System.Data.OleDb.OleDbCommand
        Dim M3Reader As OleDb.OleDbDataReader = Nothing

        'Open connection to M3
        M3Cmd.Connection = M3Con

        M3Con.Open()

        'Get all opened records
        M3Cmd.CommandText = "SELECT JLENV FROM F4001 WHERE JLUSER = '" & s(1).ToUpper & "'"
        M3Reader = M3Cmd.ExecuteReader

        While M3Reader.Read
            Me.BarListItem3.Strings.Add(Trim(M3Reader(0).ToString))
        End While

        M3Con.Close()
    End Sub

    Private Sub BarListItem3_ListItemClick(sender As System.Object, e As DevExpress.XtraBars.ListItemClickEventArgs) Handles BarListItem3.ListItemClick

        'get environment
        My.Settings.Env = (TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(4, 4)
        My.Settings.DIVI = (TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3)

        My.Settings.CADISConnectionStringP001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "CADISConnectionStringP001")
        My.Settings.M3ConnectP001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "M3ConnectP001")
        My.Settings.MySQLP001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "MySQLP001")

        My.Settings.CADISConnectionStringM001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "CADISConnectionStringM001")
        My.Settings.M3ConnectM001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "M3ConnectM001")
        My.Settings.MySQLM001 = GetConString((TryCast(e.Item, DevExpress.XtraBars.BarListItem)).Strings(e.Index).Substring(0, 3), "MySQLM001")

        If My.Settings.Env = "P001" Then
            My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringP001
            My.Settings.M3Connect = My.Settings.M3ConnectP001
            My.Settings.MySQL = My.Settings.MySQLP001
        Else
            My.Settings.CADISConnectionString = My.Settings.CADISConnectionStringM001
            My.Settings.M3Connect = My.Settings.M3ConnectM001
            My.Settings.MySQL = My.Settings.MySQLM001
        End If

        Me.Label3.Text = "Restart"
        My.Settings.Save()
        Application.Restart()

    End Sub

    Function GetConString(ByVal DIVI As String, ByVal JDNAME As String)

        GetConString = ""

        Dim M3Con As New System.Data.OleDb.OleDbConnection(My.Settings.M3ConnectUser)
        Dim M3Cmd As New System.Data.OleDb.OleDbCommand
        Dim M3Reader As OleDb.OleDbDataReader = Nothing

        'Open connection to M3
        M3Cmd.Connection = M3Con

        M3Con.Open()

        'Get all opened records
        M3Cmd.CommandText = "SELECT JDCSTRING  FROM F4002 WHERE JDDIVI = '" & Trim(DIVI) & "' AND JDNAME = '" & Trim(JDNAME) & "'"
        M3Reader = M3Cmd.ExecuteReader

        While M3Reader.Read
            GetConString = (Trim(M3Reader(0).ToString))
        End While

    End Function

    Public Function ScreenResolution() As String
        Dim intX As Integer = Screen.PrimaryScreen.Bounds.Width
        Dim intY As Integer = Screen.PrimaryScreen.Bounds.Height
        Return intX & " X " & intY
    End Function

  

    Private Sub M3OrderNumberLabel1_DoubleClick(sender As Object, e As System.EventArgs) Handles M3OrderNumberLabel1.DoubleClick
        Me.TextBox2.Text = Me.M3OrderNumberLabel1.Text.ToString
        Me.TextBox2.Focus()

    End Sub

    Private Sub BarButtonItem20_ItemClick_1(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem20.ItemClick
        M3Contract.Show()

    End Sub

    Private Sub BarButtonItem21_ItemClick_1(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem21.ItemClick
        NoContracts.Show()

    End Sub

    Private Sub BarButtonItem34_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem34.ItemClick
        OpenMap.Show()

    End Sub

    Private Sub BarButtonItem35_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem35.ItemClick
        Statistics1("30", "S91", "30 - Open maintenances in M3 only", "XTitle", "# of of open maintenances in M3 only")
    End Sub

    Private Sub BarButtonItem36_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem36.ItemClick
        Spare.Show()

    End Sub

    Private Sub BarButtonItem37_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem37.ItemClick
        PSPProposal.Show()

    End Sub

    Private Sub BarButtonItem38_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem38.ItemClick
        Boni.Show()
    End Sub

    Private Sub SplitContainerControl1_SplitterPositionChanged(sender As Object, e As System.EventArgs) Handles SplitContainerControl1.SplitterPositionChanged
        My.Settings.Form1split1 = Me.SplitContainerControl1.SplitterPosition
    End Sub

    Private Sub SplitContainerControl2_SplitterPositionChanged(sender As Object, e As System.EventArgs) Handles SplitContainerControl2.SplitterPositionChanged
        My.Settings.Form1split2 = Me.SplitContainerControl2.SplitterPosition
    End Sub
End Class